package vn.ugame.model.dal;

import java.io.UnsupportedEncodingException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import vn.ugame.exception.DataConnectionFailed;
import vn.ugame.exception.InvalidEntityClass;
import vn.ugame.model.entity.*;

public class StoreDA {

    public Item findLocalItem(String id, String name)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {
        Item item = null;
        List<Item> items = findLocalItems(id, null, null, null, null, name, null, null, null, null, null);
        if (items.size() > 0) {
            item = items.get(0);
        }
        return item;
    }

    public Item findLocalItem(String id, Locale locale)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {
        Item item = null;
        List<Item> items = findLocalItems(id, null, null, null, locale, null, null, null, null, null, null);
        if (items.size() > 0) {
            item = items.get(0);
        }
        return item;
    }

    public Category findLocalCategory(String id, Locale locale)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {
        Category category = null;
        List<Category> categories = findLocalCategories(id, null, null, locale.getLanguage());
        if (categories.size() > 0) {
            category = categories.get(0);
        }
        return category;
    }

    public Category findLocalCategory(String id, String name)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {
        Category category = null;
        List<Category> categories = findLocalCategories(id, name, null, null);
        if (categories.size() > 0) {
            category = categories.get(0);
        }
        return category;
    }

    public List<Category> findLocalCategories(String id, String name,
            String text, String locale)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {
        List<Category> categoryList = new ArrayList<Category>();

        Connection conn = ConnectionFactory.getInstance().createDBConnection();

        String query = "{ call findLocalCategories(?,?,?,?) }";
        CallableStatement cs = conn.prepareCall(query);
        cs.setString(1, id);
        cs.setString(2, name);
        cs.setString(3, text);
        cs.setString(4, locale);

        ResultSet rs = cs.executeQuery();
        EntityFactory factory = EntityFactory.createEntityFactory();
        while (rs.next()) {
            Category category = factory.createEntity(rs, Category.class);
            categoryList.add(category);
        }
        rs.close();
        cs.close();
        conn.close();
        return categoryList;
    }

    public List<Comment> findComments(String id, String itemId,
            String customerId, java.util.Date createdDate, String content,
            Integer fromIndex, Integer toIndex, IntWapper count)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {

        List<Comment> commentList = new ArrayList<Comment>();

        Connection conn = ConnectionFactory.getInstance().createDBConnection();

        String query = "{ call findComments(?,?,?,?,?,?,?,?) }";
        CallableStatement cs = conn.prepareCall(query);
        cs.setString(1, id);
        cs.setString(2, itemId);
        cs.setString(3, customerId);
        if (createdDate == null) {
            cs.setNull(4, Types.DATE);
        } else {
            cs.setDate(4, new java.sql.Date(createdDate.getTime()));
        }
        cs.setString(5, content);
        if (fromIndex == null) {
            cs.setInt(6, 0);
        } else {
            cs.setInt(6, fromIndex.intValue());
        }
        if (toIndex == null) {
            cs.setInt(7, 0);
        } else {
            cs.setInt(7, toIndex.intValue());
        }
        cs.registerOutParameter(8, Types.INTEGER);

        ResultSet rs = cs.executeQuery();
        EntityFactory factory = EntityFactory.createEntityFactory();
        while (rs.next()) {
            Comment comment = factory.createEntity(rs, Comment.class);
            commentList.add(comment);
        }

        if (count != null) {
            count.setValue(cs.getInt(8));
        }

        rs.close();
        cs.close();
        conn.close();
        return commentList;
    }

    public List<Image> findItemImages(Integer id, String itemId,
            Integer type, String path)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {

        List<Image> imageList = new ArrayList<Image>();

        Connection conn = ConnectionFactory.getInstance().createDBConnection();

        String query = "{ call findImages(?,?,?,?) }";
        CallableStatement cs = conn.prepareCall(query);
        if (id == null) {
            cs.setNull(1, Types.INTEGER);
        } else {
            cs.setInt(1, id.intValue());
        }
        cs.setString(2, itemId);
        if (type == null) {
            cs.setNull(3, Types.INTEGER);
        } else {
            cs.setInt(3, type.intValue());
        }
        cs.setString(4, path);

        ResultSet rs = cs.executeQuery();
        EntityFactory factory = EntityFactory.createEntityFactory();

        while (rs.next()) {
            Image image = factory.createEntity(rs, Image.class);
            imageList.add(image);
        }

        rs.close();
        cs.close();
        conn.close();

        return imageList;
    }

    public List<Item> findLocalItems(String id, String moduleId, String categoryId,
            Integer status, Locale locale, String name, String text, Double price,
            Integer fromIndex, Integer toIndex, IntWapper count)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {

        List<Item> itemList = new ArrayList<Item>();

        Connection conn = ConnectionFactory.getInstance().createDBConnection();

        String query = "{ call findLocalItems(?,?,?,?,?,?,?,?,?,?,?) }";
        CallableStatement cs = conn.prepareCall(query);
        cs.setString(1, id);
        cs.setString(2, moduleId);
        cs.setString(3, categoryId);
        if (status == null) {
            cs.setNull(4, Types.INTEGER);
        } else {
            cs.setInt(4, status.intValue());
        }
        if (locale == null) {
            cs.setNull(5, Types.VARCHAR);
        } else {
            cs.setString(5, locale.getLanguage());
        }
        cs.setString(6, name);
        cs.setString(7, text);
        if (price == null) {
            cs.setNull(8, Types.DOUBLE);
        } else {
            cs.setDouble(8, price.doubleValue());
        }
        if (fromIndex == null) {
            cs.setInt(9, 0);
        } else {
            cs.setInt(9, fromIndex.intValue());
        }
        if (toIndex == null) {
            cs.setInt(10, 0);
        } else {
            cs.setInt(10, toIndex.intValue());
        }
        cs.registerOutParameter(11, Types.INTEGER);

        ResultSet rs = cs.executeQuery();
        EntityFactory factory = EntityFactory.createEntityFactory();
        while (rs.next()) {
            Item item = factory.createEntity(rs, Item.class);
            itemList.add(item);
        }

        if (count != null) {
            count.setValue(cs.getInt(11));
        }

        rs.close();
        cs.close();
        conn.close();

        return itemList;
    }

    public List<Item> findSliderLocalItems(String moduleId, Locale locale, Integer type, Integer top)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {

        List<Item> itemList = new ArrayList<Item>();

        Connection conn = ConnectionFactory.getInstance().createDBConnection();

        String query = "{ call findSliderLocalItems(?,?,?,?) }";
        CallableStatement cs = conn.prepareCall(query);
        cs.setString(1, moduleId);
        if (locale == null) {
            cs.setNull(2, Types.VARCHAR);
        } else {
            cs.setString(2, locale.getLanguage());
        }
        if (type == null) {
            cs.setNull(3, Types.INTEGER);
        } else {
            cs.setInt(3, type.intValue());
        }
        if (top == null) {
            cs.setNull(4, Types.INTEGER);
        } else {
            cs.setInt(4, top.intValue());
        }

        ResultSet rs = cs.executeQuery();
        EntityFactory factory = EntityFactory.createEntityFactory();
        while (rs.next()) {
            Item item = factory.createEntity(rs, Item.class);
            itemList.add(item);
        }
        
        rs.close();
        cs.close();
        conn.close();

        return itemList;
    }
    
    public List<Item> findLocalRelatedItems(String id, Locale locale)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {

        List<Item> itemList = new ArrayList<Item>();

        Connection conn = ConnectionFactory.getInstance().createDBConnection();

        String query = "{ call findLocalRelatedItems(?,?) }";
        CallableStatement cs = conn.prepareCall(query);
        cs.setString(1, id);
        if (locale == null) {
            cs.setNull(2, Types.VARCHAR);
        } else {
            cs.setString(2, locale.getLanguage());
        }

        ResultSet rs = cs.executeQuery();
        EntityFactory factory = EntityFactory.createEntityFactory();
        while (rs.next()) {
            Item item = factory.createEntity(rs, Item.class);
            itemList.add(item);
        }

        rs.close();
        cs.close();
        conn.close();

        return itemList;
    }

    public List<Item> searchItems(String tag, String moduleId, Locale locale,
            Integer fromIndex, Integer toIndex, IntWapper count)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {

        List<Item> itemList = new ArrayList<Item>();

        Connection conn = ConnectionFactory.getInstance().createDBConnection();

        String query = "{ call searchItems(?,?,?,?,?,?) }";
        CallableStatement cs = conn.prepareCall(query);
        if (tag != null) {
            try {
                cs.setBytes(1, tag.getBytes("UTF-8"));
            } catch (UnsupportedEncodingException ex) {
            }
        } else {
            cs.setNull(1, Types.VARCHAR);
        }
        cs.setString(2, moduleId);
        if (locale == null) {
            cs.setNull(3, Types.VARCHAR);
        } else {
            cs.setString(3, locale.getLanguage());
        }
        if (fromIndex == null) {
            cs.setInt(4, 0);
        } else {
            cs.setInt(4, fromIndex.intValue());
        }
        if (toIndex == null) {
            cs.setInt(5, 0);
        } else {
            cs.setInt(5, toIndex.intValue());
        }
        cs.registerOutParameter(6, Types.INTEGER);

        ResultSet rs = cs.executeQuery();
        EntityFactory factory = EntityFactory.createEntityFactory();
        while (rs.next()) {
            Item item = factory.createEntity(rs, Item.class);
            itemList.add(item);
        }

        if (count != null) {
            count.setValue(cs.getInt(6));
        }

        rs.close();
        cs.close();
        conn.close();

        return itemList;
    }

    public void createDownloadItemLog(String itemId, String accountId)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {

        Connection conn = ConnectionFactory.getInstance().createDBConnection();

        String query = "{ call createDownload(?,?) }";
        CallableStatement cs = conn.prepareCall(query);
        cs.setString(1, itemId);
        cs.setString(2, accountId);

        cs.execute();

        cs.close();
        conn.close();
    }

    public void createComment(String itemId, String customerId, String content)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {

        Connection conn = ConnectionFactory.getInstance().createDBConnection();

        String query = "{ call createComment(?,?,?) }";
        CallableStatement cs = conn.prepareCall(query);
        cs.setString(1, itemId);
        cs.setString(2, customerId);
        cs.setString(3, content);

        cs.execute();

        cs.close();
        conn.close();
    }
}
